package com.Servlet;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.util.CellRangeAddress;

import com.jdbc.Jdbc;

/**
 * Servlet implementation class personcheckinfoOutput
 */
@WebServlet("/personcheckinfoOutput")
public class personcheckinfoOutput extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public personcheckinfoOutput() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		response.getWriter().append("Served at: ").append(request.getContextPath());
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

		request.setCharacterEncoding("utf-8");
		int columns = 0;// 用于记录查询的表格中的属性值的个数
//		String stuid = request.getParameter("stuid");
//		String stuname = request.getParameter("stuname");
		
		String findtable = "select * from information_schema.columns where table_name='check_info'";
		String find = "select*from check_info ";
		Jdbc ft = new Jdbc();
		List<String> cn = new ArrayList<String>();// 用于存储表格中的列名
		
		try {
			ft.Jdbc_Q2(findtable);

			while (ft.rs.next()) {
				columns++;// 用于记录表格中的属性的个数
				if(ft.rs.getString("column_name").equals("count")) {
					cn.add("总应到次数");
				}
				else if(ft.rs.getString("column_name").equals("absence")) {
					cn.add("出勤次数");
				}
				else {
				cn.add(ft.rs.getString("column_name"));
				}

			}
			System.out.println("属性列个数：" + columns);
		} catch (ClassNotFoundException | SQLException e) {

			e.printStackTrace();
		}
		
		// 如果没有月份选择的话，那么就直接导出所有的数据
		
			// 设置文件的名字
			response.addHeader("Content-Disposition", "attachment; filename=checkinfo.xls");
			String headline = "全体学生的考勤信息";

			// 创建EXCEL工作簿
			HSSFWorkbook workbook = new HSSFWorkbook();
			// 创建一个工作sheet
			HSSFSheet sheet = workbook.createSheet();
			//sheet.setColumnWidth(0, 20* 256); //设置sheet中第0列column宽度
			CellStyle cellStyle = workbook.createCellStyle();
			DataFormat format = workbook.createDataFormat();
			cellStyle.setDataFormat(format.getFormat("@"));// 默认为文本格式
			// 创建第一行
			HSSFRow row = sheet.createRow(0);
			HSSFCell cell = null;
			// 创建标题
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));
			cell = row.createCell(0);
			cell.setCellValue(headline);
			// 创建第二行
			HSSFRow rowtitle = sheet.createRow(1);
			// 插入第二行的列属性，就是属性名
			int i = 0;
			// =================建立标题栏=====================
			for (String s : cn) {
				sheet.setDefaultColumnStyle(i, cellStyle);
				sheet.setColumnWidth(i, 20* 256); //设置sheet中第i列column宽度
				cell = rowtitle.createCell(i);
				cell.setCellValue(s);
				i++;
			}
			
			Jdbc j = new Jdbc();
			ResultSet rs = null;

			try {
				j.Jdbc_Q2(find);// 查找数据库中的数据
				
				rs = j.rs;
			} catch (ClassNotFoundException | SQLException e1) {
				e1.printStackTrace();
			}

			i = 2;// 第三行开始才是数据

			try {
				while (rs.next()) {
					HSSFRow nextrow = sheet.createRow(i);
					int x = 1;// 用于检测循环时候列属性的个数
					while (x <= columns) {
						HSSFCell cell2 = nextrow.createCell(x - 1);
						if(x==3) {
							cell2.setCellValue(rs.getInt(3)-1);//表示应该签到的总次数
							
						}
						else if(x==4) {
							cell2.setCellValue(rs.getInt(3)-1-rs.getInt(4));//表示出勤次数
						}
						else
							cell2.setCellValue(rs.getString(x));
						x++;
					}

					i++;

				}
			} catch (SQLException e) {

				e.printStackTrace();
			}
			workbook.write(response.getOutputStream());
	
	}

}
